package com.kgc.dao;

import com.kgc.entity.Gonggao;
import com.kgc.entity.Repair;
import com.kgc.entity.Tousu;
import com.kgc.utils.PageUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class serviceDao extends BaseDao {
    public int countGG() {
        String sql = "select count(*) from gonggao";
        try {
            ResultSet rs = super.getExecuteQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public int countWX() {
        String sql = "select count(*) from `repair`";
        try {
            ResultSet rs = super.getExecuteQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public int countTS() {
        String sql = "select count(*) from `tousu`";
        try {
            ResultSet rs = super.getExecuteQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }


    public List<Gonggao> selectGG(PageUtils pageUtils, String type) {
        List<Gonggao> list = new ArrayList<>();
        String sql = "SELECT * FROM gonggao ";
        if (type != null && !"".equals(type)) {
            sql += "where title = '" + type + "'";
        }
        sql += " ORDER BY id limit ?,? ";

        try {
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Gonggao(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4),
                        rs.getDate(5), rs.getInt(6), rs.getDate(7), rs.getInt(8)));

            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();

        }
        return null;

    }

    public List<Repair> selectWX(PageUtils pageUtils, String type) {
        List<Repair> list = new ArrayList<>();
        String sql = "SELECT r.id,r.content,u.username,r.`STATUS`,r.time,r.result from `repair` r " +
                "LEFT JOIN `user` u on r.user_id=u.id  ";

        if (type != null && !"".equals(type)) {
            sql += " where r.`STATUS` = '" + type + "'";
        }
        sql += " ORDER BY r.user_id  DESC limit ?,? ";

        try {
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Repair(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
                        rs.getDate(5), rs.getString(6)));
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();

        }
        return null;
    }

    public List<Tousu> selectTS(PageUtils pageUtils, String type) {
        List<Tousu> list = new ArrayList<>();
        String sql = "SELECT t.id,t.content,u.username,t.`STATUS`,t.time,t.result from `tousu` t " +
                "LEFT JOIN `user` u on t.user_id=u.id  ";

        if (type != null && !"".equals(type)) {
            sql += " where t.`STATUS` ='" + type + "'";
        }
        sql += " ORDER BY t.user_id  DESC limit ?,? ";
        try {
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Tousu(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4),
                        rs.getDate(5), rs.getString(6)));
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();

        }
        return null;
    }


    public int updateGGByID(Integer id, String title, String content, String status) {
        String sql = "UPDATE `wyadmin01`.`gonggao` SET `title` =?, `content` = ?,`status` = ? WHERE `id` = ?";
        return super.getExecuteUpdate(sql, title, content, status, id);
    }

    public int deleteGGByID(Integer id) {
        String sql = "DELETE from `gonggao` WHERE id=?";
        return super.getExecuteUpdate(sql, id);
    }

    public int updateWXByID(Integer id, String result, String content, String status) {
        String sql = "UPDATE `wyadmin01`.`repair` SET `content` = ?,  `status` = ?, `result` = ? WHERE `id` = ?;";
        return super.getExecuteUpdate(sql, content, status, result, id);
    }

    public int deleteWXByID(Integer id) {
        String sql = "DELETE from `repair` WHERE id=?";
        return super.getExecuteUpdate(sql, id);
    }

    public int updateTSByID(Integer id, String result, String content, String status) {
        String sql = "UPDATE `wyadmin01`.`tousu` SET `content` = ?,  `status` = ?,  `result` = ? WHERE `id` = ?";
        return super.getExecuteUpdate(sql, content, status, result, id);
    }

    public int deleteTSByID(Integer id) {
        String sql = "DELETE from `wyadmin01`.`tousu` WHERE id=?";
        return super.getExecuteUpdate(sql, id);
    }


}
